import pymysql
import datetime
import tushare as ts
import time
import random



def get_index_data():
    #指数行情数据库链接
    index_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_index', charset='utf8')
    index_cursor = index_db.cursor()

    #基础信息数据库链接
    basic_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info', charset='utf8')
    basic_cursor = basic_db.cursor()

    #从基础信息数据库导出指数信息
    index_code_sql = "SELECT CODE FROM STOCK_INDEX"
    basic_cursor.execute(index_code_sql)
    index_results = basic_cursor.fetchall()


    for index_result in index_results:
        code = index_result[0]
        # 单个指数数据表格创建
        index_create_sql = """
                                    CREATE TABLE IF NOT EXISTS `{0}` (
                                    DATE DATE NOT NULL PRIMARY KEY,
                                    OPEN FLOAT,
                                    CLOSE FLOAT,
                                    HIGH FLOAT,
                                    LOW FLOAT,
                                    VOLUME FLOAT,
                                    CODE CHAR(8)
                                    )
                                    """.format(code)
        index_cursor.execute(index_create_sql)
        index_db.commit()
        start_date = '1990-12-01'
        end_date = datetime.datetime.now().strftime('%Y-%m-%d')

        time.sleep(random.uniform(1,3.5))
        try:
            index_datas = ts.get_k_data(code,start=start_date,end=end_date,index=True)
            sql_head = "INSERT INTO `{0}` (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES ".format(code)
            for i in range(len(index_datas)):
                index_data = index_datas.iloc[i]
                date = index_data['date']
                open = index_data['open']
                close = index_data['close']
                high = index_data['high']
                low = index_data['low']
                volume = index_data['volume']
                code = index_data['code']
                insert_data = (date,open,close,high,low,volume,code)
                sql_head += str(insert_data) + ','
            sql = sql_head.rstrip(',')
            index_cursor.execute(sql)
            index_db.commit()

        except:
            sql = "INSERT INTO STOCK_ERROR (CODE,START_DATE,END_DATE) VALUES ('{0}','{1}','{2}')".format(code,start_date,end_date)
            basic_cursor.execute(sql)
            print("ERROR,{0}".format(code))
        print("{0} is finished".format(code))
    index_db.close()
    basic_db.close()




def get_hist_data():
    #基础信息数据库链接
    basic_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info', charset='utf8')
    basic_cursor = basic_db.cursor()
    #股票行情数据库链接
    hist_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_hist', charset='utf8')
    hist_cursor = hist_db.cursor()

    #从基础信息数据库导出股票代码及上市信息
    stock_info_sql = "SELECT CODE,TIMETOMARKET FROM STOCK_BASICS"

    basic_cursor.execute(stock_info_sql)
    stock_results = basic_cursor.fetchall()

    end_date = datetime.datetime.now().strftime('%Y-%m-%d')

    for stock_result in stock_results:
        code = stock_result[0]
        # 单只股票数据表格创建
        hist_create_sql = """
                                    CREATE TABLE IF NOT EXISTS `{0}` (
                                    DATE DATE NOT NULL PRIMARY KEY,
                                    OPEN FLOAT,
                                    CLOSE FLOAT,
                                    HIGH FLOAT,
                                    LOW FLOAT,
                                    VOLUME FLOAT,
                                    CODE CHAR(6)
                                    )
                                    """.format(code)

        hist_cursor.execute(hist_create_sql)
        hist_db.commit()
        timetomarket = stock_result[1]
        time_to_market = timetomarket[0:4] + '-' + timetomarket[4:6] + '-' + timetomarket[6:8]
        time.sleep(random.uniform(1, 3.5))

        try:
            stock_hists = ts.get_k_data(code,start=time_to_market,end=end_date)
            sql_head = "INSERT INTO `{0}` (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES ".format(code)
            for i in range(len(stock_hists)):
                stock_hist = stock_hists.iloc[i]
                date = stock_hist['date']
                open = stock_hist['open']
                close = stock_hist['close']
                high = stock_hist['high']
                low = stock_hist['low']
                volume = stock_hist['volume']
                code = stock_hist['code']
                insert_data = (date,open,close,high,low,volume,code)
                sql_head += str(insert_data) + ","
            sql = sql_head.rstrip(',')
            hist_cursor.execute(sql)
            hist_db.commit()
        except:
            sql = "INSERT INTO STOCK_ERROR (CODE,START_DATE,END_DATE) VALUES ('{0}','{1}','{2}')".format(code,time_to_market,end_date)
            basic_cursor.execute(sql)
            print("ERROR,{0}".format(code))
        print("{0} is finished".format(code))
    hist_db.close()
    basic_db.close()

if __name__ == '__main__':
    get_index_data()
    get_hist_data()




